﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class RoleDAL
    {
        private const string SQL_SELECT_WS_ROLE = "select ROL_ROLE_ID as ROLE_ID,ROL_ROLE_NAME as ROLE_NAME,ROL_ROLE_LEVEL as ROLE_LEVEL,ROL_RCRE_STAFF_ID as RCRE_STAFF_ID,ROL_RCRE_TIME as RCRE_TIME,ROL_LCHG_STAFF_ID as LCHG_STAFF_ID,ROL_LCHG_TIME as LCHG_TIME,ROL_FOLLOW_FLG as FOLLOW_FLG from WS_ROLE where 1=1";
        private const string SQL_INSERT_WS_ROLE = "insert into WS_ROLE(ROL_ROLE_ID,ROL_ROLE_NAME,ROL_ROLE_LEVEL,ROL_RCRE_STAFF_ID,ROL_RCRE_TIME,ROL_FOLLOW_FLG) VALUES(@RoleId,@RoleName,@RoleLevel,@LastStaffId,@LastTime,@FollowFlg)";
        private const string SQL_UPDATE_WS_ROLE = "update WS_ROLE set ROL_ROLE_NAME=@RoleName,ROL_ROLE_LEVEL=@RoleLevel,ROL_LCHG_STAFF_ID=@LastStaffId,ROL_LCHG_TIME=@LastTime,ROL_FOLLOW_FLG=@FollowFlg where 1=1";
        private const string SQL_DELETE_WS_ROLE = "delete from WS_ROLE where 1=1";
        private const string SQL_COUNT_WS_ROLE = "select count(ROL_ROLE_ID) from WS_ROLE where 1=1";

        private const string SQL_WHERE_ROLE_ID = " and ROL_ROLE_ID=@RoleId ";
        private const string SQL_WHERE_ROLE_LEVEL_NOT_LESS = " and ROL_ROLE_LEVEL>=@RoleLevel ";
        private const string SQL_WHERE_ROLE_ID_NAME = " and ROL_ROLE_ID<>@RoleId and ROL_ROLE_NAME=@RoleName ";

        private const string PARM_ROLE_ID = "@RoleId";
        private const string PARM_ROLE_NAME = "@RoleName";
        private const string PARM_ROLE_LEVEL = "@RoleLevel";
        private const string PARM_LAST_STAFF_ID = "@LastStaffId";
        private const string PARM_LAST_TIME = "@LastTime";
        private const string PARM_FOLLOW_FLG = "@FollowFlg";

        Nullable<DateTime> NullDateTime = null;
        private static RoleDAL instance = new RoleDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static RoleDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public DataSet GetDataSet(int level)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_ROLE);
            sbSQL.Append(SQL_WHERE_ROLE_LEVEL_NOT_LESS);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_LEVEL, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = level });

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public List<RoleModel> Get(int level)
        {
            using (DataSet ds = GetDataSet(level))
            {
                DataTable dt = ds.Tables[0];
                List<RoleModel> list = dt.AsEnumerable().Select(t => new RoleModel
                {
                    Id = Convert.ToString(t.ItemArray[0]),
                    Name = Convert.ToString(t.ItemArray[1]),
                    Level = Convert.ToInt32(t.ItemArray[2]),
                    CreatedStaffId = Convert.ToString(t.ItemArray[3]),
                    CreatedDate = Convert.ToDateTime(t.ItemArray[4]),
                    LastModifiedStaffId = t.IsNull(5) ? string.Empty : Convert.ToString(t.ItemArray[5]),
                    LastModifiedDate = t.IsNull(6) ? NullDateTime : Convert.ToDateTime(t.ItemArray[6]),
                    Follow = t.IsNull(7) ? true : string.Equals(Convert.ToString(t.ItemArray[7]), "Y")
                }).ToList<RoleModel>();

                return list;
            }
        }
        public RoleModel Get(string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_ROLE);
            sbSQL.Append(SQL_WHERE_ROLE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray()))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow t = ds.Tables[0].Rows[0];
                    return new RoleModel()
                    {
                        Id = Convert.ToString(t.ItemArray[0]),
                        Name = Convert.ToString(t.ItemArray[1]),
                        Level = Convert.ToInt32(t.ItemArray[2]),
                        CreatedStaffId = Convert.ToString(t.ItemArray[3]),
                        CreatedDate = Convert.ToDateTime(t.ItemArray[4]),
                        LastModifiedStaffId = t.IsNull(5) ? string.Empty : Convert.ToString(t.ItemArray[5]),
                        LastModifiedDate = t.IsNull(6) ? NullDateTime : Convert.ToDateTime(t.ItemArray[6]),
                        Follow = t.IsNull(7) ? true : string.Equals(Convert.ToString(t.ItemArray[7]), "Y")
                    };
                }
                return null;
            }
        }
        public int Insert(DbTransaction trans, RoleModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_ROLE_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_ROLE_LEVEL, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.Level });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });
            parms.Add(new MySqlParameter(PARM_FOLLOW_FLG, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Follow ? "Y" : "N" });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_ROLE, parms.ToArray());
        }
        public int Update(DbTransaction trans, RoleModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_WS_ROLE);
            sbSQL.Append(SQL_WHERE_ROLE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_ROLE_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_ROLE_LEVEL, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.Level });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });
            parms.Add(new MySqlParameter(PARM_FOLLOW_FLG, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Follow ? "Y" : "N" });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public int Delete(DbTransaction trans, string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_DELETE_WS_ROLE);
            sbSQL.Append(SQL_WHERE_ROLE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_ROLE);
            sbSQL.Append(SQL_WHERE_ROLE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(string id, string name)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_ROLE);
            sbSQL.Append(SQL_WHERE_ROLE_ID_NAME);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });
            parms.Add(new MySqlParameter(PARM_ROLE_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = name });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
    }
}
